﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using Z.BulkOperations;

namespace FFF.Test.YCmd.Tests
{
    // https://bulk-operations.net/bulk-insert
    public static class BulkOptionHelper
    {
        private static string conStr = "server=.;database=Test;uid=sa;pwd=Aa12344321";

        //        public static void BullkInsertTable()
        //        {
        //            GenerateDatabase();

        //            var customers = GenerateCustomers(2);

        //            using (var connection = new SqlConnection(conStr))
        //            {
        //                connection.Open();

        //                // SAVE customers
        //                using (var bulk = new BulkOperation(connection))
        //                {
        //                    // easy to use
        //                    bulk.DestinationTableName = "Customers";
        //                    bulk.BulkInsert(customers);
        //                }

        //                customers = GenerateCustomers(5);

        //                // SAVE customers
        //                using (var bulk = new BulkOperation(connection))
        //                {
        //                    // easy to customize
        //                    bulk.DestinationTableName = "Customers";
        //                    bulk.InsertIfNotExists = true;
        //                    bulk.BulkInsert(customers);
        //                }

        //                // SHOW data
        //                using (var command = connection.CreateCommand())
        //                {
        //                    command.CommandText = @"
        //SELECT * FROM Customers
        //";
        //                    var ds = new DataSet();
        //                    var sqlDataAdapter = new SqlDataAdapter(command);
        //                    sqlDataAdapter.Fill(ds);

        //                    Console.WriteLine(ds.Tables[0].Rows.Count);

        //                }
        //            }
        //        }

        public static void BullkInsertTable()
        {
            GenerateDatabase();

            var customers = GenerateCustomers(2);

            using (var connection = new SqlConnection(conStr))
            {
                connection.Open();

                // SAVE customers
                using (var bulk = new BulkOperation(connection))
                {
                    // easy to use
                    bulk.DestinationTableName = "Customers";
                    bulk.BulkInsert(customers);
                }

                customers = GenerateCustomers(5);

                // SAVE customers
                using (var bulk = new BulkOperation(connection))
                {
                    // easy to customize
                    bulk.DestinationTableName = "Customers";
                    bulk.InsertIfNotExists = true;
                    bulk.BulkInsert(customers);
                }

                // SHOW data
                using (var command = connection.CreateCommand())
                {
                    command.CommandText = @"
SELECT * FROM Customers
";
                    var ds = new DataSet();
                    var sqlDataAdapter = new SqlDataAdapter(command);
                    sqlDataAdapter.Fill(ds);

                    Console.WriteLine(ds.Tables[0].Rows.Count);

                }
            }
        }


        public static void BullkInsertEntity()
        {
            GenerateDatabase();

            var customers = GenerateCustomerEntitys(2);

            using (var connection = new SqlConnection(conStr))
            {
                connection.Open();

                // SAVE customers
                using (var bulk = new BulkOperation<Customer>(connection))
                {
                    // easy to use
                    bulk.DestinationTableName = "Customers";
                    bulk.BulkInsert(customers);
                }

                customers = GenerateCustomerEntitys(5);

                // SAVE customers
                using (var bulk = new BulkOperation<Customer>(connection))
                {
                    // easy to customize
                    bulk.DestinationTableName = "Customers";
                    bulk.InsertIfNotExists = true;
                    bulk.BulkInsert(customers);
                }

                // SHOW data
                using (var command = connection.CreateCommand())
                {
                    command.CommandText = @"
SELECT * FROM Customers
";
                    var ds = new DataSet();
                    var sqlDataAdapter = new SqlDataAdapter(command);
                    sqlDataAdapter.Fill(ds);

                    ds.Tables[0].TableName = "1 - Customers";

                }
            }
        }



        public static List<Customer> GenerateCustomerEntitys(int count)
        {
            List<Customer> list = new List<Customer>();

            for (int i = 0; i < count; i++)
            {
                list.Add(new Customer() { CustomerID = (i + 1), Name = "Customer_" + i });
            }

            return list;
        }

        public static DataTable GenerateCustomers(int count)
        {
            DataTable dtTable = new DataTable("Customers");

            dtTable.Columns.Add("CustomerID", typeof(int));
            dtTable.Columns.Add("Name", typeof(string));

            for (int i = 0; i < count; i++)
            {
                var drCustomer = dtTable.NewRow();
                drCustomer["CustomerID"] = (i + 1);
                drCustomer["Name"] = "Customer_" + i;
                dtTable.Rows.Add(drCustomer);
            }

            return dtTable;
        }

        public static void GenerateDatabase()
        {
            using (var connection = new SqlConnection(conStr))
            {
                connection.Open();
                using (var command = connection.CreateCommand())
                {
                    command.CommandText = @"   
IF  EXISTS ( SELECT  name FROM    sys.columns WHERE   object_id = OBJECT_ID(N'Customers') ) DROP TABLE Customers;
CREATE TABLE [Customers]
(
	[CustomerID] [INT] IDENTITY(1,1) NOT NULL,
	[Name] [NVARCHAR](MAX) NULL,
	CONSTRAINT [PK_Customers] PRIMARY KEY CLUSTERED 
	(
		[CustomerID] ASC
	)
)
 ";
                    command.ExecuteNonQuery();
                }
            }
        }
    }

    public class Customer
    {
        public int CustomerID { get; set; }
        public string Name { get; set; }
    }
}
